package db

import (
	"context"

	"github.com/google/uuid"
	"github.com/lib/pq"
)

const tagMVPInfoWithCount = `-- name : TagMVPInfoWithCount :many
SELECT uid, username, tail, honor, level, tags , countnum
FROM SOCAIL , 
	(SELECT USERID,
	 count(userid) AS countnum
		FROM GALLERY
		WHERE $1 = ANY(TAGS)
		GROUP BY USERID
		ORDER BY COUNT(USERID) DESC
		LIMIT 5) ORDEREDUSER
where socail.uid = ordereduser.userid
`

func (q *Queries) TagMVPInfoWithCount(ctx context.Context, dollar_1 interface{}) ([]CrossSocail, error) {
	rows, err := q.db.QueryContext(ctx, tagMVPInfoWithCount, dollar_1)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []CrossSocail
	for rows.Next() {
		var i CrossSocail
		if err := rows.Scan(
			&i.Uid,
			&i.Username,
			&i.Tail,
			&i.Honor,
			&i.Level,
			pq.Array(&i.Tags),
			&i.Countnum,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const tagMVPInfo = `-- name : TagMVPInfo :many
SELECT uid, username, tail, honor, level, tags 
FROM SOCAIL
WHERE UID IN
	(SELECT USERID
		FROM GALLERY
		WHERE $1 = ANY(TAGS)
		GROUP BY USERID
		ORDER BY COUNT(USERID) DESC
		LIMIT 5)
`

func (q *Queries) TagMVPInfo(ctx context.Context, dollar_1 interface{}) ([]Socail, error) {
	rows, err := q.db.QueryContext(ctx, tagMVPInfo, dollar_1)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Socail
	for rows.Next() {
		var i Socail
		if err := rows.Scan(
			&i.Uid,
			&i.Username,
			&i.Tail,
			&i.Honor,
			&i.Level,
			pq.Array(&i.Tags),
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const subListSocailInfo = `-- name : SubListSocailInfo :many
select uid, username, tail, honor, level, tags
from socail
where uid = ANY (
	select unnest(sublist::uuid[])
	from subs
	where uid = $1
)
`

func (q *Queries) SubListSocailInfo(ctx context.Context, userid uuid.UUID) ([]Socail, error) {
	rows, err := q.db.QueryContext(ctx, subListSocailInfo, userid)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Socail
	for rows.Next() {
		var i Socail
		if err := rows.Scan(
			&i.Uid,
			&i.Username,
			&i.Tail,
			&i.Honor,
			&i.Level,
			pq.Array(&i.Tags),
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const subGallery = `-- name : SubGallery :many
select gid, images, tags, userid, username, date, token, nice, title
from gallery where userid IN
(SELECT unnest(array_append(sublist::uuid[], $1::uuid)) FROM subs
WHERE uid = $1)
order by date desc
`

func (q *Queries) SubGallery(ctx context.Context, target uuid.UUID) ([]Gallery, error) {
	rows, err := q.db.QueryContext(ctx, subGallery, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Gallery
	for rows.Next() {
		var i Gallery
		if err := rows.Scan(
			&i.Gid,
			pq.Array(&i.Images),
			pq.Array(&i.Tags),
			&i.Userid,
			&i.Username,
			&i.Date,
			&i.Token,
			&i.Nice,
			&i.Title,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const statReply = `-- name : StatReply :many
select count(replys.date) as countdate, replys.date from replys, gallery
where replys.gid = gallery.gid
AND gallery.userid = $1
group by replys.date
order by replys.date 
`

func (q *Queries) StatReply(ctx context.Context, target uuid.UUID) ([]StatDate, error) {
	rows, err := q.db.QueryContext(ctx, statReply, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []StatDate
	for rows.Next() {
		var i StatDate
		if err := rows.Scan(
			&i.Countdate,
			&i.Date,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const statGallery = `-- name : StatGallery :many
select count(date) as countdate ,date from gallery
where userid = $1
group by date
order by date 
`

func (q *Queries) StatGallery(ctx context.Context, target uuid.UUID) ([]StatDate, error) {
	rows, err := q.db.QueryContext(ctx, statGallery, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []StatDate
	for rows.Next() {
		var i StatDate
		if err := rows.Scan(
			&i.Countdate,
			&i.Date,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const statCom = `-- name : StatCom :many
select count(replys.uid) as countuid, replys.uid from replys, gallery
where replys.gid = gallery.gid
AND gallery.userid = $1
group by replys.uid
order by count(replys.uid) DESC
`

func (q *Queries) StatCom(ctx context.Context, target uuid.UUID) ([]StatUid, error) {
	rows, err := q.db.QueryContext(ctx, statCom, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []StatUid
	for rows.Next() {
		var i StatUid
		if err := rows.Scan(
			&i.Countuid,
			&i.Uid,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const statComName = `-- name : StatComName :many
select count(replys.uid) as countuid, replys.username, replys.uid from replys, gallery
where replys.gid = gallery.gid
AND gallery.userid = $1
group by replys.uid, replys.username
order by count(replys.uid) DESC
`

func (q *Queries) StatComName(ctx context.Context, target uuid.UUID) ([]StatUsername, error) {
	rows, err := q.db.QueryContext(ctx, statComName, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []StatUsername
	for rows.Next() {
		var i StatUsername
		if err := rows.Scan(
			&i.Countuid,
			&i.Username,
			&i.Uid,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}
